package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

public class SparkUtils {
    private final static Logger LOGGER = LogManager.getLogger(SparkUtils.class);
	private static String driver = ProUtil.getValue("sparkDriver");
	private static String url = ProUtil.getValue("sparkUrl");
	private static String user = ProUtil.getValue("sparkUser");
	private static String password = ProUtil.getValue("sparkPassword");
    
	public SparkUtils(){};
	
	static{
		/*
		 * 驱动注册
		 */
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
            LOGGER.error(e.getMessage());
			throw new ExceptionInInitializerError(e);
		} 
	}
	
    /**
     * 获取 Connetion
     * @return
     * @throws SQLException
     */
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, user, password);
	}
	
    /**
     * 关闭连接
     * @return
     * @throws SQLException
     */
	public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
		close((Statement) null, ps, rs, conn);
	}

	public static void close(Connection conn, Statement statement, ResultSet rs) {
		close(statement, (PreparedStatement) null, rs, conn);
	}

	public static void close(PreparedStatement ps, ResultSet rs, Connection conn) {
		close((Statement) null, ps, rs, conn);
	}

	public static void close(Statement statement, PreparedStatement ps, ResultSet rs, Connection conn) {
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException var8) {
				;
			}

			statement = null;
		}

		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException var7) {
				;
			}

			rs = null;
		}

		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException var6) {
				;
			}

			ps = null;
		}

		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException var5) {
			}
			conn = null;
		}

	}
	
    /**
     * SELECT: 查询多行多列的值，将其封装到 Map 对象的 List 集合中
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static int queryCount(String sql, List<Object> params) throws SQLException {
        Connection connection = SparkUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        try {
            ps = connection.prepareStatement(sql);
            fillPs(ps, params);
            rs = ps.executeQuery();
            if (rs != null) {
            	while(rs.next()){
            		count = rs.getInt(1);
            	}
            }
        } catch (SQLException e) {
            LOGGER.error(e.getMessage());
            throw e;
        } finally {
        	SparkUtils.close(connection, ps, rs);
        }
        return count;
    }
	
    /**
     * SELECT: 查询多行多列的值，将其封装到 Map 对象的 List 集合中
     *
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static List<Map<String, Object>> selectMapListDB(String sql, List<Object> params) throws SQLException {
        List<Map<String, Object>> resultList = new ArrayList<>();
        Connection connection = SparkUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            fillPs(ps, params);
            rs = ps.executeQuery();
            if (rs != null) {
            	resultList = DBUtil.analyzeResultSet(rs);
            }
        } catch (SQLException e) {
            LOGGER.error(e.getMessage());
            throw e;
        } finally {
        	SparkUtils.close(connection, ps, rs);
        }
        return resultList;
    }
    
    
    /**
     * 填充sql参数
     *
     * @param ps     : 预处理语句
     * @param params : 填充sql占位符
     * @throws SQLException : 处理异常
     */
    private static void fillPs(PreparedStatement ps, List<Object> params) throws SQLException {
        if (null == ps || null == params || params.size() == 0) {
            return;
        }
        for (int i = 0; i < params.size(); i++) {
            ps.setObject(i + 1, params.get(i));
        }
    }
    
    public static void main(String[] args) throws Exception{
    	Connection conn = SparkUtils.getConnection();
       // PreparedStatement pstmt = conn.prepareStatement("select * from view_loginlist");
        PreparedStatement pstmt = conn.prepareStatement("select * from(select row_number() over(order by time desc) r,* from logf where day>'2018-07-07' and tid=1)t where r between 4 and 6");

        long start = System.currentTimeMillis();
        ResultSet rs = pstmt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getInt(1)+"|"+rs.getString(2)+"|"+rs.getString(3)
            +"|"+rs.getString(4)+"|"
//                            +rs.getString(5)
            );
        }
        System.out.println("TIME:"+(System.currentTimeMillis()-start));
        rs.close();
        pstmt.close();
        conn.close();
    }
}
